package org.gulugulubiao;

import lombok.RequiredArgsConstructor;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.gulugulubiao.entity.JudgeResult;
import org.gulugulubiao.entity.User;
import org.gulugulubiao.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@SpringBootTest
// @RequiredArgsConstructor
public class testSelectProblem {
    private static final String url = "jdbc:mysql://120.79.72.63:3307/sqlOnline";
    private static final String username = "root";
    private static final String password = "sqlOJ2025$$02qweokn!@#&1das";
    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    @Test
    public void test2() throws Exception {
        String inputAns = "SELECT student_id, name, gender, birth_date, email FROM students WHERE student_id IN (1,2,3,4,5)";
        String rightAns = "SELECT name, student_id as id, gender, birth_date, email FROM students WHERE student_id IN (1,2,3,4,5)";
        System.out.println(judgeSelect(inputAns, rightAns));

    }

    /**
     * 判断输入的select语句是否正确
     *
     * @param inputAns 用户输入的答案
     * @param rightAns 正确答案
     * @return 正确答案
     */
    private JudgeResult judgeSelect(String inputAns, String rightAns) throws Exception {
        // 注册驱动，新版驱动类（如果显式加载驱动）
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 获取连接
        Connection connection = DriverManager.getConnection(url, username, password);
        // 禁用自动提交，手动管理事务
        connection.setAutoCommit(false);
        // 定义要执行的sql
        String sql = inputAns;

        // 获取执行SQL的对象
        Statement statement = connection.createStatement();

        // 执行SQL
        ResultSet resultSet = statement.executeQuery(sql); // 执行查询

        // 获取元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount(); // 获取列数
        List<Map<String, Object>> inputResultList = new ArrayList<>();

        // 动态处理结果
        while (resultSet.next()) {
            Map<String, Object> rowMap = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i); // 获取列名
                Object value = resultSet.getObject(i); // 获取列值
                rowMap.put(columnName, value); // 存储列名和值
            }
            inputResultList.add(rowMap); // 将当前行数据添加到结果列表
        }

        // 执行正确sql
        sql = rightAns;
        resultSet = statement.executeQuery(sql); // 执行查询
        // 获取元数据
        metaData = resultSet.getMetaData();
        // 获取列数
        columnCount = metaData.getColumnCount();
        List<Map<String, Object>> rightResultList = new ArrayList<>();
        // 动态处理结果
        while (resultSet.next()) {
            Map<String, Object> rowMap = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i); // 获取列名
                Object value = resultSet.getObject(i); // 获取列值
                rowMap.put(columnName, value); // 存储列名和值
            }
            rightResultList.add(rowMap); // 将当前行数据添加到结果列表
        }

        JudgeResult judgeResult = new JudgeResult();
        judgeResult.setAnswer(rightAns);
        if (compareResultLists(inputResultList, rightResultList)) {
            judgeResult.setFlag(true);
        } else {
            judgeResult.setFlag(false);
        }

        return judgeResult;
    }

    // 比较两个List<Map<String, Object>>是否相等
    private boolean compareResultLists(List<Map<String, Object>> list1, List<Map<String, Object>> list2) {
        if (list1.size() != list2.size()) {
            return false; // 行数不同
        }

        // 逐行比较，假设行的顺序可以不同
        List<Map<String, Object>> copyOfList2 = new ArrayList<>(list2);

        for (Map<String, Object> row1 : list1) {
            boolean matchFound = false;
            for (Map<String, Object> row2 : copyOfList2) {
                if (row1.equals(row2)) {
                    copyOfList2.remove(row2);
                    matchFound = true;
                    break;
                }
            }
            if (!matchFound) {
                return false; // 没找到匹配的行
            }
        }
        return true; // 所有行均匹配
    }
}
